import pymysql
from common.readConfig import WRConfigFile
from logs.logsfile import logger


class SQL:
    @logger.catch()
    def __init__(self):
        conf = WRConfigFile().read_conf
        host = conf('database', 'host_17')
        user = conf('database', 'user_17')
        password = conf('database', 'password_17')
        base_name = conf('database', 'base_name_17')
        try:
            logger.info('正在连接数据库,数据库地址={host},用户名={user},数据库密码={password},数据库名称={base_name}',
                        host=host, user=user, password=password, base_name=base_name)
            self.connection = pymysql.connect(
                port=3306,
                host=host,
                user=user,
                password=password,
                database=base_name,
                cursorclass=pymysql.cursors.DictCursor
            )
        except BaseException as e:
            logger.warning('数据库连接时发生异常:{e}', e=e)
        else:
            logger.success('连接数据库成功')
        self.cursor = None

    def creat_connect(self):
        """
        创建连接
        @return: 游标cursor
        """
        self.cursor = self.connection.cursor()
        return self.cursor

    def close_base(self):
        """
        关闭连接
        @return: None
        """
        self.connection.close()
        logger.success('关闭数据库成功')

    @logger.catch()
    def select_one(self, table_name='testcase', field_name='title', value_name=None):
        """
        根据where条件查询数据
        @param table_name: 表名
        @param field_name: 查询字段名
        @param value_name: 字段值
        @return: 查询结果（字典组成的列表，每个查询结果为一个字典）
        """
        cursors = self.creat_connect()
        sql = 'select * from %s where %s = "%s"' % (table_name, field_name, value_name)
        try:
            cursors.execute(sql)
            logger.success('执行查询SQL语句:{sql}', sql=sql)
        except BaseException as e:
            logger.warning('SQL语句执行异常，请检查SQL语句:{sql},异常:{e}', sql=sql, e=e)
        else:
            result = cursors.fetchall()
            logger.success('数据库返回查询数据:{result}', result=result)
            return result
        finally:
            cursors.close()
            self.close_base()

    @logger.catch()
    def select_all(self, table_name=None):
        """
        查询表中所有数据
        @param table_name: 表名
        @return: 查询结果（字典组成的列表，每个查询结果为一个字典）
        """
        cursors = self.creat_connect()
        sql = 'select * from %s' % table_name
        try:
            cursors.execute(sql)
            logger.success('执行查询SQL语句:{sql}', sql=sql)
        except BaseException as e:
            logger.warning('SQL语句执行异常，请检查SQL语句:{sql},异常:{e}', sql=sql, e=e)
        else:
            all_result = cursors.fetchall()
            logger.success('数据库返回查询数据:{all_result}', all_result=all_result)
            return all_result
        finally:
            cursors.close()
            self.close_base()

    def delete_one(self, table_name='testcase', field_name=None, value_name=None):
        """
        根据条件删除数据
        @param table_name: 表名
        @param field_name: 字段名
        @param value_name: 字段值
        @return: 删除的数据组成的列表
        """
        cursors = self.creat_connect()
        sql = 'delete from %s where %s = "%s"' % (table_name, field_name, value_name)
        try:
            cursors.execute(sql)
            self.connection.commit()
            # logger.success('执行查询SQL语句:{sql}', sql=sql)
        except BaseException as e:
            logger.warning('SQL语句执行异常，请检查SQL语句:{sql},异常:{e}', sql=sql, e=e)
        else:
            result = cursors.fetchall()
            # logger.success('数据库返回查询数据:{result}', result=result)
            return result
        finally:
            cursors.close()
            self.close_base()

    def update_one(self, table_name='testcase', update_field=None, update_value=None, field_name=None, value_name=None):
        """
        根据条件更新数据
        @param table_name: 表名
        @param update_field: 需要更新的字段名
        @param update_value: 更新值
        @param field_name: 查询条件字段名
        @param value_name: 查询值
        @return: 更新数据组成的列表
        """
        cursors = self.creat_connect()
        sql = 'update %s set %s = %s where %s = "%s"' % (table_name, update_field, update_value, field_name, value_name)
        try:
            cursors.execute(sql)
            self.connection.commit()
        except BaseException as e:
            logger.warning('SQL语句执行异常，请检查SQL语句:{sql},异常:{e}', sql=sql, e=e)
        else:
            result = cursors.fetchall()
            return result
        finally:
            cursors.close()
            self.close_base()

    def insert_one(self, table_name='test_case', values=None):
        """
        插入一条数据
        @param table_name: 表名
        @param values: 字符串形式的元组
        @return: 插入数据组成的元组
        """
        cursors = self.creat_connect()
        sql = f"""insert into {table_name} values {str(values)}"""
        try:
            cursors.execute(sql)
            self.connection.commit()
        except BaseException as e:
            logger.warning('SQL语句执行异常，请检查SQL语句:{sql},异常:{e}', sql=sql, e=e)
        else:
            result = cursors.fetchall()
            return result
        finally:
            cursors.close()
            self.close_base()


if __name__ == '__main__':
    SQL().select_one(table_name='testcase_cjxm', field_name='case_id', value_name='1')
    SQL().select_one()
    SQL().select_all(table_name="testcase_cjxm")
